package zy.dao.money.property.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.money.property.PropertyDAO;
import zy.entity.money.property.T_Money_Property;

@Repository
public class PropertyDAOImpl extends BaseDaoImpl implements PropertyDAO{

	@Override
	public List<T_Money_Property> list(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pp_id,pp_code,pp_name,pp_spell,pp_shop_code,pp_type,companyid");
		sql.append(" FROM t_money_property t");
		sql.append(" WHERE 1 = 1");
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.pp_code,:searchContent)>0 OR INSTR(t.pp_name,:searchContent)>0 OR INSTR(t.pp_spell,:searchContent)>0)");
        }
        sql.append(" AND pp_type=:pp_type");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY pp_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Money_Property.class));
	}

	@Override
	public Integer queryByName(T_Money_Property property) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pp_id FROM t_money_property");
		sql.append(" WHERE 1=1");
		if(null != property.getPp_name() && !"".equals(property.getPp_name())){
			sql.append(" AND pp_name=:pp_name");
		}
		if(null != property.getPp_id() && property.getPp_id() > 0){
			sql.append(" AND pp_id <> :pp_id");
		}
		sql.append(" AND pp_type=:pp_type");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(property),Integer.class);
			return id;
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Money_Property queryByID(Integer pp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pp_id,pp_code,pp_name,pp_spell,pp_shop_code,pp_type,companyid ");
		sql.append(" FROM t_money_property");
		sql.append(" WHERE pp_id=:pp_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("pp_id", pp_id),
					new BeanPropertyRowMapper<>(T_Money_Property.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Money_Property property) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(pp_code+0)) FROM t_money_property ");
		sql.append(" WHERE 1=1");
		sql.append(" AND pp_type=:pp_type");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(property), String.class);
		property.setPp_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_money_property");
		sql.append(" (pp_code,pp_name,pp_spell,pp_shop_code,pp_type,companyid)");
		sql.append(" VALUES(:pp_code,:pp_name,:pp_spell,:pp_shop_code,:pp_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(property),holder);
		int id = holder.getKey().intValue();
		property.setPp_id(id);
	}

	@Override
	public void update(T_Money_Property property) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_property");
		sql.append(" SET pp_name=:pp_name");
		sql.append(" ,pp_spell=:pp_spell");
		sql.append(" WHERE pp_id=:pp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(property));
	}

	@Override
	public void del(Integer pp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_property");
		sql.append(" WHERE pp_id=:pp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pp_id", pp_id));
	}

	@Override
	public Integer checkUse_Expense(String pp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) FROM(");
		sql.append(" (SELECT 1 FROM t_money_expenselist WHERE epl_mp_code = :pp_code AND companyid = :companyid LIMIT 1)");
		sql.append(" UNION ALL");
		sql.append(" (SELECT 1 FROM t_buy_feelist WHERE fel_mp_code = :pp_code AND companyid = :companyid LIMIT 1)");
		sql.append(" UNION ALL");
		sql.append(" (SELECT 1 FROM t_batch_feelist WHERE fel_mp_code = :pp_code AND companyid = :companyid LIMIT 1)");
		sql.append(" UNION ALL");
		sql.append(" (SELECT 1 FROM t_sort_feelist WHERE fel_mp_code = :pp_code AND companyid = :companyid LIMIT 1)");
		sql.append(" )t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("pp_code", pp_code).addValue("companyid", companyid),Integer.class);
	}
	@Override
	public Integer checkUse_Income(String pp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) FROM(");
		sql.append(" SELECT 1 FROM t_money_incomelist WHERE icl_mp_code = :pp_code AND companyid = :companyid LIMIT 1");
		sql.append(" )t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("pp_code", pp_code).addValue("companyid", companyid),Integer.class);
	}

}
